
<?php


 /**
  * Customer Data Access Object (DAO).
  * This class contains all database handling that is needed to 
  * permanently store and retrieve Customer object instances. 
  */


class CustomerDao {


    /**
     * createValueObject-method. This method is used when the Dao class needs
     * to create new value object instance. The reason why this method exists
     * is that sometimes the programmer may want to extend also the valueObject
     * and then this method can be overrided to return extended valueObject.
     * NOTE: If you extend the valueObject class, make sure to override the
     * clone() method in it!
     */
    function createValueObject() {
          return new Customer();
    }


    /**
     * getObject-method. This will create and load valueObject contents from database 
     * using given Primary-Key as identifier. This method is just a convenience method 
     * for the real load-method which accepts the valueObject as a parameter. Returned
     * valueObject will be created using the createValueObject() method.
     */
    function getObject(&$conn, $CustomerID) {
        try
        {

          $valueObject = $this->createValueObject();
          $valueObject->setCustomerID($CustomerID);
          $this->load(&$conn, &$valueObject);
          return $valueObject;
        }
        catch (Exception $e)
        {
            return false;
        }
    }


    /**
     * load-method. This will load valueObject contents from database using
     * Primary-Key as identifier. Upper layer should use this so that valueObject
     * instance is created and only primary-key should be specified. Then call
     * this method to complete other persistent information. This method will
     * overwrite all other fields except primary-key and possible runtime variables.
     * If load can not find matching row, NotFoundException will be thrown.
     *
     * @param conn         This method requires working database connection.
     * @param valueObject  This parameter contains the class instance to be loaded.
     *                     Primary-key field must be set for this to work properly.
     */
    function load(&$conn, &$valueObject) {
        try
        {

          if (!$valueObject->getCustomerID()) {
               //print "Can not select without Primary-Key!";
               return false;
          }

          $sql = "SELECT * FROM Customer WHERE (CustomerID = ".$valueObject->getCustomerID().") "; 

          if ($this->singleQuery(&$conn, $sql, &$valueObject))
               return true;
          else
               return false;
        }
        catch (Exception $e)
        {
            return false;
        }
    }


    /**
     * LoadAll-method. This will read all contents from database table and
     * build an Vector containing valueObjects. Please note, that this method
     * will consume huge amounts of resources if table has lot's of rows. 
     * This should only be used when target tables have only small amounts
     * of data.
     *
     * @param conn         This method requires working database connection.
     */
    function loadAll(&$conn) {
        try
        {


          $sql = "SELECT * FROM Customer ORDER BY CustomerID ASC ";

          $searchResults = $this->listQuery(&$conn, $sql);

          return $searchResults;
        }
        catch (Exception $e)
        {
            return false;
        }
    }



    /**
     * create-method. This will create new row in database according to supplied
     * valueObject contents. Make sure that values for all NOT NULL columns are
     * correctly specified. Also, if this table does not use automatic surrogate-keys
     * the primary-key must be specified. After INSERT command this method will 
     * read the generated primary-key back to valueObject if automatic surrogate-keys
     * were used. 
     *
     * @param conn         This method requires working database connection.
     * @param valueObject  This parameter contains the class instance to be created.
     *                     If automatic surrogate-keys are not used the Primary-key 
     *                     field must be set for this to work properly.
     */
    function create(&$conn, &$valueObject) {
        try
        {

        try
        {
          $sql = "INSERT INTO Customer (LastName, FirstName, ";
          $sql = $sql."Address, City, State, ";
          $sql = $sql."Zip, PhoneNumber1, PhoneNumber2, ";
          $sql = $sql."PhoneNumber3, Email) VALUES (";
          $sql = $sql."'".$valueObject->getLastName()."', ";
          $sql = $sql."'".$valueObject->getFirstName()."', ";
          $sql = $sql."'".$valueObject->getAddress()."', ";
          $sql = $sql."'".$valueObject->getCity()."', ";
          $sql = $sql."'".$valueObject->getState()."', ";
          $sql = $sql."'".$valueObject->getZip()."', ";
          $sql = $sql."'".$valueObject->getPhoneNumber1()."', ";
          $sql = $sql."'".$valueObject->getPhoneNumber2()."', ";
          $sql = $sql."'".$valueObject->getPhoneNumber3()."', ";
          $sql = $sql."'".$valueObject->getEmail()."') ";
          $result = $this->databaseUpdate(&$conn, $sql);
        }
        catch(Exception $eError)
        {
            return false;
        }

          return true;
        }
        catch (Exception $e)
        {
            return false;
        }
    }


    /**
     * save-method. This method will save the current state of valueObject to database.
     * Save can not be used to create new instances in database, so upper layer must
     * make sure that the primary-key is correctly specified. Primary-key will indicate
     * which instance is going to be updated in database. If save can not find matching 
     * row, NotFoundException will be thrown.
     *
     * @param conn         This method requires working database connection.
     * @param valueObject  This parameter contains the class instance to be saved.
     *                     Primary-key field must be set for this to work properly.
     */
    function save(&$conn, &$valueObject) {
        try
        {

          $sql = "UPDATE Customer SET LastName = '".$valueObject->getLastName()."', ";
          $sql = $sql."FirstName = '".$valueObject->getFirstName()."', ";
          $sql = $sql."Address = '".$valueObject->getAddress()."', ";
          $sql = $sql."City = '".$valueObject->getCity()."', ";
          $sql = $sql."State = '".$valueObject->getState()."', ";
          $sql = $sql."Zip = '".$valueObject->getZip()."', ";
          $sql = $sql."PhoneNumber1 = '".$valueObject->getPhoneNumber1()."', ";
          $sql = $sql."PhoneNumber2 = '".$valueObject->getPhoneNumber2()."', ";
          $sql = $sql."PhoneNumber3 = '".$valueObject->getPhoneNumber3()."', ";
          $sql = $sql."Email = '".$valueObject->getEmail()."'";
          $sql = $sql." WHERE (CustomerID = ".$valueObject->getCustomerID().") ";
          $result = $this->databaseUpdate(&$conn, $sql);

          if ($result != 1) {
               //print "PrimaryKey Error when updating DB!";
               return false;
          }

          return true;
        }
        catch (Exception $e)
        {
            return false;
        }
    }


    /**
     * delete-method. This method will remove the information from database as identified by
     * by primary-key in supplied valueObject. Once valueObject has been deleted it can not 
     * be restored by calling save. Restoring can only be done using create method but if 
     * database is using automatic surrogate-keys, the resulting object will have different 
     * primary-key than what it was in the deleted object. If delete can not find matching row,
     * NotFoundException will be thrown.
     *
     * @param conn         This method requires working database connection.
     * @param valueObject  This parameter contains the class instance to be deleted.
     *                     Primary-key field must be set for this to work properly.
     */
    function delete(&$conn, &$valueObject) {
        try
        {


          if (!$valueObject->getCustomerID()) {
               //print "Can not delete without Primary-Key!";
               return false;
          }

          $sql = "DELETE FROM Customer WHERE (CustomerID = ".$valueObject->getCustomerID().") ";
          $result = $this->databaseUpdate(&$conn, $sql);

          if ($result != 1) {
               //print "PrimaryKey Error when updating DB!";
               return false;
          }
          return true;
        }
        catch(Exception $e)
        {
            return false;
        }
    }


    /**
     * deleteAll-method. This method will remove all information from the table that matches
     * this Dao and ValueObject couple. This should be the most efficient way to clear table.
     * Once deleteAll has been called, no valueObject that has been created before can be 
     * restored by calling save. Restoring can only be done using create method but if database 
     * is using automatic surrogate-keys, the resulting object will have different primary-key 
     * than what it was in the deleted object. (Note, the implementation of this method should
     * be different with different DB backends.)
     *
     * @param conn         This method requires working database connection.
     */
    function deleteAll(&$conn) {
        try
        {

          $sql = "DELETE FROM Customer";
          $result = $this->databaseUpdate(&$conn, $sql);

          return true;
        }
        catch(Exception $e)
        {
            return false;
        }
    }


    /**
     * coutAll-method. This method will return the number of all rows from table that matches
     * this Dao. The implementation will simply execute "select count(primarykey) from table".
     * If table is empty, the return value is 0. This method should be used before calling
     * loadAll, to make sure table has not too many rows.
     *
     * @param conn         This method requires working database connection.
     */
    function countAll(&$conn) {
        try
        {

          $sql = "SELECT count(*) FROM Customer";
          $allRows = 0;

          $result = $conn->execute($sql);

          if ($row = $conn->nextRow($result))
                $allRows = $row[0];

          return $allRows;
        }
        catch (Exception $e)
        {
            return false;
        }
    }


    /** 
     * searchMatching-Method. This method provides searching capability to 
     * get matching valueObjects from database. It works by searching all 
     * objects that match permanent instance variables of given object.
     * Upper layer should use this by setting some parameters in valueObject
     * and then  call searchMatching. The result will be 0-N objects in vector, 
     * all matching those criteria you specified. Those instance-variables that
     * have NULL values are excluded in search-criteria.
     *
     * @param conn         This method requires working database connection.
     * @param valueObject  This parameter contains the class instance where search will be based.
     *                     Primary-key field should not be set.
     */
    function searchMatching(&$conn, &$valueObject) {

        try
        {

          $first = true;
          $sql = "SELECT * FROM Customer WHERE 1=1 ";

          if ($valueObject->getCustomerID() != 0) {
              if ($first) { $first = false; }
              $sql = $sql."AND CustomerID = ".$valueObject->getCustomerID()." ";
          }

          if ($valueObject->getLastName() != "") {
              if ($first) { $first = false; }
              $sql = $sql."AND LastName LIKE '".$valueObject->getLastName()."%' ";
          }

          if ($valueObject->getFirstName() != "") {
              if ($first) { $first = false; }
              $sql = $sql."AND FirstName LIKE '".$valueObject->getFirstName()."%' ";
          }

          if ($valueObject->getAddress() != "") {
              if ($first) { $first = false; }
              $sql = $sql."AND Address LIKE '".$valueObject->getAddress()."%' ";
          }

          if ($valueObject->getCity() != "") {
              if ($first) { $first = false; }
              $sql = $sql."AND City LIKE '".$valueObject->getCity()."%' ";
          }

          if ($valueObject->getState() != "") {
              if ($first) { $first = false; }
              $sql = $sql."AND State LIKE '".$valueObject->getState()."%' ";
          }

          if ($valueObject->getZip() != "") {
              if ($first) { $first = false; }
              $sql = $sql."AND Zip LIKE '".$valueObject->getZip()."%' ";
          }

          if ($valueObject->getPhoneNumber1() != "") {
              if ($first) { $first = false; }
              $sql = $sql."AND PhoneNumber1 LIKE '".$valueObject->getPhoneNumber1()."%' ";
          }

          if ($valueObject->getPhoneNumber2() != "") {
              if ($first) { $first = false; }
              $sql = $sql."AND PhoneNumber2 LIKE '".$valueObject->getPhoneNumber2()."%' ";
          }

          if ($valueObject->getPhoneNumber3() != "") {
              if ($first) { $first = false; }
              $sql = $sql."AND PhoneNumber3 LIKE '".$valueObject->getPhoneNumber3()."%' ";
          }

          if ($valueObject->getEmail() != "") {
              if ($first) { $first = false; }
              $sql = $sql."AND Email LIKE '".$valueObject->getEmail()."%' ";
          }


          $sql = $sql."ORDER BY CustomerID ASC ";

          // Prevent accidential full table results.
          // Use loadAll if all rows must be returned.
          if ($first)
               return array();

          $searchResults = $this->listQuery(&$conn, $sql);

          return $searchResults;
        }
        catch (Exception $e)
        {
            return false;
        }
    }

    /**
     * databaseUpdate-method. This method is a helper method for internal use. It will execute
     * all database handling that will change the information in tables. SELECT queries will
     * not be executed here however. The return value indicates how many rows were affected.
     * This method will also make sure that if cache is used, it will reset when data changes.
     *
     * @param conn         This method requires working database connection.
     * @param stmt         This parameter contains the SQL statement to be excuted.
     */
    function databaseUpdate(&$conn, &$sql) {

          $result = $conn->execute($sql);

          return $result;
    }



    /**
     * databaseQuery-method. This method is a helper method for internal use. It will execute
     * all database queries that will return only one row. The resultset will be converted
     * to valueObject. If no rows were found, NotFoundException will be thrown.
     *
     * @param conn         This method requires working database connection.
     * @param stmt         This parameter contains the SQL statement to be excuted.
     * @param valueObject  Class-instance where resulting data will be stored.
     */
    function singleQuery(&$conn, &$sql, &$valueObject) {

          $result = $conn->execute($sql);

          if ($row = $conn->nextRow($result)) {

                   $valueObject->setCustomerID($row[0]); 
                   $valueObject->setLastName($row[1]); 
                   $valueObject->setFirstName($row[2]); 
                   $valueObject->setAddress($row[3]); 
                   $valueObject->setCity($row[4]); 
                   $valueObject->setState($row[5]); 
                   $valueObject->setZip($row[6]); 
                   $valueObject->setPhoneNumber1($row[7]); 
                   $valueObject->setPhoneNumber2($row[8]); 
                   $valueObject->setPhoneNumber3($row[9]); 
                   $valueObject->setEmail($row[10]); 
          } else {
               //print " Object Not Found!";
               return false;
          }
          return true;
    }


    /**
     * databaseQuery-method. This method is a helper method for internal use. It will execute
     * all database queries that will return multiple rows. The resultset will be converted
     * to the List of valueObjects. If no rows were found, an empty List will be returned.
     *
     * @param conn         This method requires working database connection.
     * @param stmt         This parameter contains the SQL statement to be excuted.
     */
    function listQuery(&$conn, &$sql) {

          $searchResults = array();
          $result = $conn->execute($sql);

          while ($row = $conn->nextRow($result)) {
               $temp = $this->createValueObject();

               $temp->setCustomerID($row[0]); 
               $temp->setLastName($row[1]); 
               $temp->setFirstName($row[2]); 
               $temp->setAddress($row[3]); 
               $temp->setCity($row[4]); 
               $temp->setState($row[5]); 
               $temp->setZip($row[6]); 
               $temp->setPhoneNumber1($row[7]); 
               $temp->setPhoneNumber2($row[8]); 
               $temp->setPhoneNumber3($row[9]); 
               $temp->setEmail($row[10]); 
               array_push($searchResults, $temp);
          }

          return $searchResults;
    }
}

?>
